package org.makcro.sms.services.chat.base.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.makcro.sms.services.chat.base.dto.HistoryItem;
import org.makcro.sms.services.chat.model.Agent;

public class AstroWholeMessageQuestionDao extends QuestionDAO {

	@Override
	public List<HistoryItem> findByAgent(Agent agent) throws SQLException {
		ArrayList<org.makcro.sms.services.chat.base.dto.HistoryItem> result = new ArrayList<org.makcro.sms.services.chat.base.dto.HistoryItem>();
		String query = "SELECT q.QuestionID, q.Question, q.DateReceived, q.FromNumber, "
				+ "(select a1.AnswerID from answer a1 where a1.AnswerID = (select MIN(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as AnswerID, "
				+ "(select a1.Answer from answer a1 where a1.AnswerID = (select MIN(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as Answer, "
				+ "(select a1.DateAnswered from answer a1 where a1.AnswerID = (select MIN(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as DateAnswered, "
				+ "(select a1.AnswerDelivered from answer a1 where a1.AnswerID = (select MIN(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as AnswerDelivered "
				+ "FROM question as q INNER JOIN answer as a ON q.QuestionID = a.QuestionID INNER JOIN questionresponsibility qr ON q.QuestionID = qr.QuestionID " + "WHERE qr.AgentID = ? "
				+ "AND a.Visable = 1 " + "GROUP BY q.QuestionID " + "ORDER BY a.DateAnswered DESC " + "LIMIT 0, 100";

		Connection connection = this.dataSource.getConnection();
		PreparedStatement statement = connection.prepareStatement(query);
		statement.setInt(1, agent.getAgentID());
		ResultSet resultSet = statement.executeQuery();
		while (resultSet.next()) {
			Integer questionID = resultSet.getInt("QuestionID");
			String question = resultSet.getString("Question");
			Timestamp dateReceived = resultSet.getTimestamp("DateReceived");
			String fromNumber = resultSet.getString("FromNumber");
			Integer answerID = resultSet.getInt("AnswerID");
			String answer = resultSet.getString("answer");
			Timestamp dateAnswered = resultSet.getTimestamp("DateAnswered");
			boolean answerDelivered = resultSet.getBoolean("AnswerDelivered");

			org.makcro.sms.services.chat.base.dto.HistoryItem q = new org.makcro.sms.services.chat.base.dto.HistoryItem();
			q.setQuestionId(questionID);
			q.setQuestion(question);
			q.setDateReceived(dateReceived);
			q.setFromNumber(fromNumber);
			q.setAnswerId(answerID);
			q.setAnswer(answer);
			q.setDateAnswered(dateAnswered);
			q.setAnswerDelivered(answerDelivered);

			result.add(q);
		}
		connection.close();
		return result;
	}
	
	
	@Override
	public List<HistoryItem> findAnswered() throws SQLException {
		ArrayList<org.makcro.sms.services.chat.base.dto.HistoryItem> result = new ArrayList<org.makcro.sms.services.chat.base.dto.HistoryItem>();
		String query = "SELECT q.QuestionID, q.Question, q.DateReceived, q.FromNumber, "
				+ "(select a1.AnswerID from answer a1 where a1.AnswerID = (select MIN(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as AnswerID, "
				+ "(select a1.Answer from answer a1 where a1.AnswerID = (select MIN(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as Answer, "
				+ "(select a1.DateAnswered from answer a1 where a1.AnswerID = (select MIN(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as DateAnswered, "
				+ "(select a1.AnswerDelivered from answer a1 where a1.AnswerID = (select MIN(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as AnswerDelivered "
				+ "FROM question as q INNER JOIN answer as a ON q.QuestionID = a.QuestionID " + "WHERE a.Visable = 1 " + "GROUP BY q.QuestionID " + "ORDER BY a.DateAnswered DESC " + "LIMIT 0, 100";
		Connection connection = this.dataSource.getConnection();
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(query);
		while (resultSet.next()) {
			Integer questionID = resultSet.getInt("QuestionID");
			String question = resultSet.getString("Question");
			Timestamp dateReceived = resultSet.getTimestamp("DateReceived");
			String fromNumber = resultSet.getString("FromNumber");
			Integer answerID = resultSet.getInt("AnswerID");
			String answer = resultSet.getString("answer");
			Timestamp dateAnswered = resultSet.getTimestamp("DateAnswered");
			boolean answerDelivered = resultSet.getBoolean("AnswerDelivered");

			org.makcro.sms.services.chat.base.dto.HistoryItem q = new org.makcro.sms.services.chat.base.dto.HistoryItem();
			q.setQuestionId(questionID);
			q.setQuestion(question);
			q.setDateReceived(dateReceived);
			q.setFromNumber(fromNumber);
			q.setAnswerId(answerID);
			q.setAnswer(answer);
			q.setDateAnswered(dateAnswered);
			q.setAnswerDelivered(answerDelivered);

			result.add(q);
		}
		connection.close();
		return result;
	}
	
	@Override
	public List<HistoryItem> findHistory(String phoneNumber) throws SQLException {
		ArrayList<org.makcro.sms.services.chat.base.dto.HistoryItem> result = new ArrayList<org.makcro.sms.services.chat.base.dto.HistoryItem>();
		String query = "SELECT q.QuestionID, q.Question, q.DateReceived, q.FromNumber, "
				+ "(select a1.AnswerID from answer a1 where a1.AnswerID = (select MIN(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as AnswerID, "
				+ "(select a1.Answer from answer a1 where a1.AnswerID = (select MIN(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as Answer, "
				+ "(select a1.DateAnswered from answer a1 where a1.AnswerID = (select MIN(a1.AnswerID) from answer a1 where a1.QuestionID = q.QuestionID)) as DateAnswered "
				+ "FROM question as q INNER JOIN answer as a ON q.QuestionID = a.QuestionID " + "where q.FromNumber = ? " + "AND a.Visable = 1 " + "GROUP BY q.QuestionID "
				+ "ORDER BY a.DateAnswered DESC " + "LIMIT 0, 100";

		Connection connection = this.dataSource.getConnection();
		PreparedStatement statement = connection.prepareStatement(query);
		statement.setString(1, phoneNumber);
		ResultSet resultSet = statement.executeQuery();
		while (resultSet.next()) {
			Integer questionID = resultSet.getInt("QuestionID");
			String question = resultSet.getString("Question");
			Timestamp dateReceived = resultSet.getTimestamp("DateReceived");
			String fromNumber = resultSet.getString("FromNumber");
			Integer answerID = resultSet.getInt("AnswerID");
			String answer = resultSet.getString("answer");
			Timestamp dateAnswered = resultSet.getTimestamp("DateAnswered");

			org.makcro.sms.services.chat.base.dto.HistoryItem q = new org.makcro.sms.services.chat.base.dto.HistoryItem();
			q.setQuestionId(questionID);
			q.setQuestion(question);
			q.setDateReceived(dateReceived);
			q.setFromNumber(fromNumber);
			q.setAnswerId(answerID);
			q.setAnswer(answer);
			q.setDateAnswered(dateAnswered);

			result.add(q);
		}
		connection.close();
		return result;
	}
	
}
